/*
 * @Author: your name
 * @Date: 2022-02-13 14:59:14
 * @LastEditTime: 2022-02-27 09:45:37
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: \bg-values\main.go
 */
package main

import (
	"fmt"
	"io/ioutil"
	"path/filepath"
	"sort"
	"strconv"
	"strings"

	log "github.com/sirupsen/logrus"
	"github.com/xuri/excelize/v2"
)

func main() {
	dir := "D:\\coding\\backgroud\\reports"
	// xls2xlsx(dir)

	//遍历目录下所有文件
	items, err := ioutil.ReadDir(dir)
	if err != nil {
		log.Error(err)
	}
	pathFiles := make(map[string]string)
	for _, item := range items {
		if item.IsDir() {
			name := item.Name()
			subDir := filepath.Join(dir, name)
			subItems, err := ioutil.ReadDir(subDir)
			if err != nil {
				log.Error(err)
				continue
			}
			for _, subItem := range subItems {
				if subItem.IsDir() {
					continue
				}
				subName := subItem.Name()
				ext := strings.ToLower(filepath.Ext(subName))
				if ext == ".xlsx" {
					subFilePath := filepath.Join(subDir, subName)
					pathFiles[name[:7]] = subFilePath
				}
			}
		}
	}

	sortKeys := []string{}
	for k := range pathFiles {
		sortKeys = append(sortKeys, k)
	}
	sort.Strings(sortKeys)
	fout, err := excelize.OpenFile("all29.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer fout.Close()

	// defColNames := []string{"实验室编号", "样品原标识", "样品性状", "检测项目"}
	rows, _ := fout.Rows("Sheet1")
	for rows.Next() {
		row, err := rows.Columns()
		if err != nil {
			fmt.Println(err)
		}
		num := rows.CurrentRow()
		if num < 5 {
			continue
		}

		sheetName := "Sheet" + strconv.Itoa(num)
		fout.NewSheet(sheetName)
		// cols := map[string]string{
		// 	"A1": "实验室编号", "B1": "样品原标识", "C1": "样品性状", "D1": "检测项目"}
		// cols["E1"] = row[0]
		// for k, v := range cols {
		// 	fout.SetCellValue(sheetName, k, v)
		// }
		// colNames := append(defColNames, row[0])
		if len(row) == 0 {
			log.Println("null row ")
			continue
		}
		err = fout.SetSheetRow(sheetName, "A1", &[]interface{}{"实验室编号", "样品原标识", "样品性状", "检测项目", row[0]})
		if err != nil {
			log.Error(err)
		}
	}
	if err = rows.Close(); err != nil {
		fmt.Println(err)
	}
	fout.Save()

	for i, k := range sortKeys {
		f, err := excelize.OpenFile(pathFiles[k])
		if err != nil {
			fmt.Println(err)
			return
		}
		defer f.Close()

		rows, err := f.Rows("工作表3")
		if err != nil {
			log.Fatal(err)
		}
		total := rows.TotalRows()
		// if len(rows) != 26 && len(rows) != 27 && len(rows) != 29 {
		// }

		switch total {
		case 26:
			err := procRows26(fout, rows)
			if err != nil {
				log.Error(err)
			}
		case 27:
		case 29:
		default:
			log.Error("total rows number error~")
		}
		var min, max int64
		for rows.Next() {
			row, err := rows.Columns()
			if err != nil {
				fmt.Println(err)
			}
			if rows.CurrentRow() < 9 {
				continue
			}
			fmt.Println(row[5:])
			for ci, col := range row {
				if col == "-" {
					fmt.Println("has null value~~~")
					break
				}
				continue
				if ci >= 5 && len(col) > 11 {
					splitNum := strings.Count(col, "-")
					if splitNum == 2 {
						//level
						lastX := strings.LastIndex(col, "-")
						l := col[lastX+1:]
						fmt.Println("col[lastX+1:],", l)
					}
					if splitNum == 3 {
						//sublevel
						lastX := strings.LastIndex(col, "-")
						l := col[lastX-1 : lastX]
						sl := col[lastX+1:]
						fmt.Println(" col[lastX-1 : lastX],col[lastX+1:],", l, sl)
					}
					level := col[11:]
					fmt.Println(col, level)
					if strings.Contains(level, "-") {
						break
					} else {
						lnum, _ := strconv.ParseInt(level, 10, 64)
						if ci == 5 {
							min = lnum
						}
						if max < lnum {
							max = lnum
						}
					}
				}
			}
			break
		}
		if err = rows.Close(); err != nil {
			fmt.Println(err)
		}
		fmt.Printf("num:%3d/%d,id:%s,原标识:%s(%d),最小层号:%d,最大层号:%d,rownum:%d \n", i, len(pathFiles), k, "cell", len("cell"), min, max, total)
	}
}
func procRows26(fout *excelize.File, rows *excelize.Rows) error {
	//26rows,9容重、11水溶性氟化物、12总氟化物三行值设置为空-
	// curOutRow := 1
	for rows.Next() {
		row, err := rows.Columns()
		if err != nil {
			fmt.Println(err)
		}
		curRowNum := rows.CurrentRow()
		if curRowNum == 5 || curRowNum == 14 {
			continue
		}
		fmt.Println(row[5:])
		min, max := getLevel(row)
		if min != 1 && (max > 10 || max < min) {
			log.Errorf("get min_level(%d) and max_level(%d) error~", min, max)
		}
		//不同行处理逻辑不同
		switch curRowNum {
		//插入容重数据
		case 8:
			// rStrs := getLevelAvg(row, min, max)
			// fout.Rows("xx")
			// err := fout.SetSheetRow("Sheet1", "F9", rStrs)

		//插入水溶性氟化物数据
		case 10:
		//插入总氟化物数据
		case 11:
		default:
		}
	}

	return nil
}

func getLevel(row []string) (min, max int) {
	min = 99
	max = -1
	for ci, col := range row {
		if ci >= 5 && len(col) > 11 {
			splitNum := strings.Count(col, "-")
			//level
			if splitNum == 2 {
				lastX := strings.LastIndex(col, "-")
				level := col[lastX+1:]
				l64, _ := strconv.ParseInt(level, 10, 64)
				if min > int(l64) {
					min = int(l64)
				}
				if max < int(l64) {
					max = int(l64)
				}
			}
			//sublevel
			if splitNum == 3 {
				lastX := strings.LastIndex(col, "-")
				level := col[lastX-1 : lastX]
				subLevel := col[lastX+1:]
				l64, _ := strconv.ParseInt(level, 10, 64)
				s64, _ := strconv.ParseInt(subLevel, 10, 64)
				fmt.Println(" col[lastX-1 : lastX],col[lastX+1:],", l64, s64)
				break
			}
		}
	}
	return
}

func getLevelAvg(row []string, min, max int) []string {
	count := 0
	vals := []float64{}
	for i := min; i < max; i++ {
		v, err := strconv.ParseFloat(row[i], 64)
		if err != nil {
			vals = append(vals, 0)
		} else {
			vals = append(vals, v)
			count++
		}
	}
	xi := 0
	for i, v := range vals {
		if v == 0 {
			continue
		}
		v2, _ := strconv.ParseFloat(row[max+1+xi], 64)
		v3, _ := strconv.ParseFloat(row[max+count+1+xi], 64)
		vals[i] = (vals[i] + v2 + v3) / 3
		xi++
	}
	if xi != count {
		log.Error("calc avg error")
	}
	rstr := []string{}
	for _, v := range vals {
		rstr = append(rstr, strconv.FormatFloat(v, 'f', 2, 32))
	}
	return rstr
}
